package com.cjack.frenchexcel.dao;

import com.cjack.frenchexcel.table.ConsumerRecordTable;
import org.hibernate.annotations.SQLUpdate;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * Created by root on 10/5/19
 */
public interface ConsumerRecordDao extends JpaRepository<ConsumerRecordTable, Long>, JpaSpecificationExecutor<ConsumerRecordTable> {

    public List<ConsumerRecordTable> findByDateDeDepense( String dateDeDepense);

    @Transactional( timeout = 100)
    @Modifying
    @Query( value = "insert into fe_consumer_record( id,carte_identifiant,collaborateur,create_time,date_de_debit,date_de_depense,description,devise_originale,fournisseur,justif,montant_charge,montant_en_devise,montantht,montantttc,nature,tvanon_recup,tvarecup,update_time,invoice,invoice_memo,invoice_review_time,invoice_status,goods_id,invoice_upload_user_id) VALUES( :#{#data.id},:#{#data.carteIdentifiant},:#{#data.collaborateur},:#{#data.createTime},:#{#data.dateDeDebit},:#{#data.dateDeDepense},:#{#data.description},:#{#data.deviseOriginale},:#{#data.fournisseur},:#{#data.justif},:#{#data.montantCharge},:#{#data.montantEnDevise},:#{#data.montantHT},:#{#data.montantTTC},:#{#data.nature},:#{#data.tVANonRecup},:#{#data.tVARecup},:#{#data.updateTime},:#{#data.invoice},:#{#data.invoiceMemo},:#{#data.invoiceReviewTime},:#{#data.invoiceStatus},:#{#data.goodsId},:#{#data.invoiceUploadUserId})", nativeQuery = true)
    void restoreData( @Param("data") ConsumerRecordTable table);

    /**
     * 清除所有数据
     */
    @Transactional( timeout = 100)
    @Modifying
    @Query( value = " delete from fe_consumer_record where 1=1", nativeQuery = true)
    void clearAll();

    /**
     * 统计传入日期到现在之间的总消费金额
     * @param importUserId
     * @param dateDeDepense  传入yyyy-MM-dd这种格式的日期，比如要统计19年10月1日到现在之间的数据，传入2019-10-01即可
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(montantttc), 2), 0)montantttc from fe_consumer_record where import_user_id=?1 and date_de_depense > ?2 and ( check_res_account < 3 or check_res_account is null)", nativeQuery = true)
    Double sumByImportUserIdAndDateDeDebitMore( Long importUserId, String dateDeDepense);

    /**
     * 统计员工消费的总额
     * @param importUserId
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(montantttc), 2), 0)montantttc from fe_consumer_record where import_user_id=?1 and ( check_res_account < 3 or check_res_account is null)", nativeQuery = true)
    Double sumByImportUserId( Long importUserId);

    /**
     * 统计总消费额
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(montantttc), 2), 0)montantttc from fe_consumer_record where check_res_account < 3 or check_res_account is null", nativeQuery = true)
    Double sum();

    /**
     * 统计员工时间段内消费的总额
     * @param importUserId
     * @return
     */
    @Query( value = " select IFNULL( ROUND( sum(montantttc), 2), 0)montantttc from fe_consumer_record where import_user_id=?1 and date_de_depense >?2 and date_de_depense < ?3 and ( check_res_account < 3 or check_res_account is null)", nativeQuery = true)
    Double sumByImportUserIdAndTime( Long importUserId, String startTime, String endTime);

    @Query( value = " select * from fe_consumer_record where id > ?1 and invoice is not null and (invoice_status is null or invoice_status != 'PASS') order by id asc limit 1", nativeQuery = true)
    ConsumerRecordTable getLastById( Long id);

    List<ConsumerRecordTable> getByOtherIdAndSourceFrom( Long otherId, String sourceFrom);

    List<ConsumerRecordTable> findAllByInvoiceIsNullAndImportUserId( Long importUserId);

    List<ConsumerRecordTable> findAllByInvoiceIsNull();

    List<ConsumerRecordTable> findByDateDeDepenseAndCarteIdentifiant( String dateDeDepense, String carteIdentifiant);

    /**
     * 统计本月已经上传发票的数量
     * @return
     */
    @Query( value = "select count(id) from fe_consumer_record WHERE DATE_FORMAT( invoice_upload_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' ) ", nativeQuery = true)
    int countInoviceNum();

    @Transactional( timeout = 100)
    @Modifying
    @Query( value = "update fe_consumer_record set invoice=null,invoice_img=null,invoice_money=null,invoice_status=null where id=:id", nativeQuery = true)
    int updateFieldById(@Param("id") Long id);
}
